﻿Imports System.Text

Namespace StatementSources
    Public Class CreateUserStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer, _
                       username As String, _
                       password As String, _
                       createdBy As Integer)
            Dim stringBuilder As New StringBuilder
            stringBuilder.Append("INSERT [dbo].[Users] ")
            stringBuilder.Append("([EmployeeId],[Username],[Password],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm]) ")
            stringBuilder.Append("VALUES (@EmployeeId,@Username,@Password,@CreatedBy,GETDATE(),@CreatedBy,GETDATE());")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@Username", username)
            Parameters.AddWithValue("@Password", password)
            Parameters.AddWithValue("@CreatedBy", createdBy)
        End Sub

    End Class

    Public Class ReadAllUserStatementSource
        Inherits BaseStatementSource

        Public Sub New()
            Dim stringBuilder As New StringBuilder
            stringBuilder.Append("SELECT [UserId],[EmployeeId],[Username],[Password],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Users];")

            Sql = stringBuilder.ToString()
        End Sub

    End Class

    Public Class ReadByEmployeeCodeForUserStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeCode As String)
            Dim stringBuilder As New StringBuilder
            stringBuilder.Append("SELECT u.[UserId],u.[EmployeeId],u.[Username],u.[Password],u.[CreatedBy],u.[CreatedDttm],u.[ModifiedBy],u.[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Users] u ")
            stringBuilder.Append("INNER JOIN [dbo].[Employees] e ")
            stringBuilder.Append("ON u.[EmployeeId]=e.[EmployeeId] ")
            stringBuilder.Append("WHERE e.[EmployeeCode]=@EmployeeCode;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeCode", employeeCode)
        End Sub

    End Class

    Public Class ReadByUserIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(userId As Integer)
            Dim stringBuilder As New StringBuilder
            stringBuilder.Append("SELECT [UserId],[EmployeeId],[Username],[Password],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Users] ")
            stringBuilder.Append("WHERE [UserId]=@UserId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@UserId", userId)
        End Sub

    End Class

    Public Class AuthenticateUserStatementSource
        Inherits BaseStatementSource

        Public Sub New(username As String, password As String)
            Dim stringBuilder As New StringBuilder
            stringBuilder.Append("SELECT [UserId],[EmployeeId],[Username],[Password],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Users] ")
            stringBuilder.Append("WHERE [Username]=@Username ")
            stringBuilder.Append("AND [Password]=@Password;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@Username", username)
            Parameters.AddWithValue("@Password", password)
        End Sub

    End Class

    Public Class UpdateUserStatementSource
        Inherits BaseStatementSource

        Public Sub New(userId As Integer, _
                       employeeId As Integer, _
                       username As String, _
                       password As String, _
                       modifiedBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("UPDATE [dbo].[Users] SET ")
            stringBuilder.Append("[EmployeeId]=ISNULL(@EmployeeId,[EmployeeId]),")
            stringBuilder.Append("[Username]=ISNULL(@Username,[Username]),")
            stringBuilder.Append("[Password]=ISNULL(@Password,[Password]),")
            stringBuilder.Append("[ModifiedBy]=@ModifiedBy,")
            stringBuilder.Append("[ModifiedDttm]=GETDATE() ")
            stringBuilder.Append("WHERE [UserId]=@UserId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@UserId", userId)
            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@Username", username)
            Parameters.AddWithValue("@Password", password)
            Parameters.AddWithValue("@ModifiedBy", modifiedBy)
        End Sub

    End Class

    Public Class DeleteUserStatementSource
        Inherits BaseStatementSource

        Public Sub New(userId As Integer)
            Sql = "DELETE FROM [dbo].[Users] WHERE [UserId]=@UserId;"
            Parameters.AddWithValue("@UserId", userId)
        End Sub

    End Class
End Namespace

